import pandas as pd
from geopy.distance import geodesic

# 读取Excel文件
result_file = pd.read_excel(r'D:\xlzx\4G5G资料20220610\结果文件.xlsx')
site_file = pd.read_excel(r'D:\xlzx\4G5G资料20220610\2025\站址存量表20250212.xlsx')

# 将铁塔站址编码改为字符串类型
result_file['铁塔站址编码'] = result_file['铁塔站址编码'].astype(str)
site_file['铁塔站址编码'] = site_file['铁塔站址编码'].astype(str)

# 以铁塔站址编码为关联键进行合并
merged_df = pd.merge(result_file, site_file, on='铁塔站址编码', how='left',suffixes=('', '_site'))

# 计算站址经纬度和经纬度的距离
def calculate_distance(row):
    if pd.isna(row['纬度']) or pd.isna(row['经度']) or pd.isna(row['站址纬度']) or pd.isna(row['站址经度']):
        return None
    return geodesic((row['纬度'], row['经度']), (row['站址纬度'], row['站址经度'])).meters

merged_df['距离'] = merged_df.apply(calculate_distance, axis=1)

# 选择只保留result_file中的列和新增的距离列
final_df = merged_df[result_file.columns.tolist() +  ['站址名称', '站址编码INTID', '距离']]

# 保存结果到新的Excel文件
final_df.to_excel(r'D:\xlzx\4G5G资料20220610\合并结果文件123.xlsx', index=False)